﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NENAData.EF;
using NENAData.Factory;
using System.Data.SqlClient;

namespace NENAData.Events
{
    public class SessionReportEntity
    {
        public int SessionId { get; set; }
        public string Speaker { get; set; }
        public DateTime? Start_Time { get; set; }
        public int Member_ID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

    public class ConferenceSummaryReportEntity
    {
        public int Payment_ID { get; set; }
        public double? Payment_Amount { get; set; }
        public int Member_ID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
        public string HomePhone { get; set; }
        public string RegistrationComments { get; set; }
    }   

    public class ConferenceReport
    {
        private static NENA_MembershipEntities GetConnection()
        {
            var x = new SQLMembership();
            return x.DBConnection();
        }

        public List<ConferenceSummaryReportEntity> GetConferenceSummaryReport()
        {
            NENA_MembershipEntities db = GetConnection();

            List<ConferenceSummaryReportEntity> oConferenceSummaryReportEntity =
                (from er in db.EventRegistrations
                 join tp in db.TransactionPayments on er.TransactionPayment.TransactionPaymentID equals tp.TransactionPaymentID
                 join m in db.Members on er.Member.Member_ID equals m.Member_ID
                 where er.Event.EventId > 7 &&
                 tp.TransactionCompleted
                 orderby m.FirstName
                 select new ConferenceSummaryReportEntity
                 {
                     Payment_Amount = tp.PaymentAmount,
                     Payment_ID = tp.TransactionPaymentID,
                     Member_ID = m.Member_ID,
                     FirstName = m.FirstName,
                     LastName = m.LastName,
                     Email = m.EmailAddress,
                     HomePhone = m.HomePhone,
                     RegistrationComments =  er.Comments
                 }
                    ).ToList();

            return oConferenceSummaryReportEntity;
        }

        public List<SessionReportEntity> GetConferenceSessionReport()
        {
            NENA_MembershipEntities db = GetConnection();

            List<SessionReportEntity> oSessionReportEntity =
                (from esr in db.EventSessionRegistrations
                 join ss in db.SlotSessions on esr.SlotSession.SessionId equals ss.SessionId
                 join er in db.EventRegistrations on esr.EventRegistration.EventRegistrationID equals er.EventRegistrationID
                 join tp in db.TransactionPayments on er.TransactionPayment.TransactionPaymentID equals tp.TransactionPaymentID
                 join m in db.Members on er.Member.Member_ID equals m.Member_ID
                 where er.Event.EventId > 7 &&
                 tp.TransactionCompleted
                 orderby esr.SlotSession.SessionId, m.FirstName
                 select new SessionReportEntity
                     {
                         SessionId = esr.SlotSession.SessionId,
                         Speaker = ss.Speaker,
                         Start_Time = ss.Time_Start,
                         Member_ID = er.Member.Member_ID,
                         FirstName = m.FirstName,
                         LastName = m.LastName                     
                     }
                    ).ToList();

            return oSessionReportEntity;
        }

        public List<EF.Member> GetSocialRegistrations()
        {
            NENA_MembershipEntities db = GetConnection();

            var oMemberTickets =
                (from er in db.EventRegistrations
                 join tp in db.TransactionPayments on er.TransactionPayment.TransactionPaymentID equals tp.TransactionPaymentID
                 join m in db.Members on er.Member.Member_ID equals m.Member_ID
                 where (er.Event.EventId == 3 || er.Event.EventId == 5) &&
                 tp.TransactionCompleted
                 orderby m.FirstName
                 select m).ToList();
            
            return oMemberTickets;

        }
    }
}
